package cn.edu.zhku.citrusmonitor.announcement.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.BeanProcessor;
import org.apache.commons.dbutils.GenerousBeanProcessor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.RowProcessor;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import cn.edu.zhku.citrusmonitor.domain.TbAnnouncement;
import cn.edu.zhku.citrusmonitor.util.C3P0Util;

public class AnnouncementDaoImpl implements AnnouncementDao{
	BeanProcessor bean = new GenerousBeanProcessor();
	RowProcessor processor = new BasicRowProcessor(bean);

	@Override
	public int addAnnounce(TbAnnouncement announce) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql="insert into announcement(title,content) values(?,?)";
		
		return qr.update(sql,announce.getTitle(),announce.getContent());
	}

	@Override
	public int removeAnnounce(String id) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql="delete from announcement where announcement_id=?";
		

		return qr.update(sql,id);
	}

	@Override
	public int modifyAnnounce(TbAnnouncement announce) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql="update announcement set title=?,content=? where announcement_id=?";
		 System.out.println(announce.getAnnouncementId());
		 System.out.println(announce.getContent());
		
		return qr.update(sql,announce.getTitle(),announce.getContent(),announce.getAnnouncementId());
	}
	//Ajax搜索框显示数据
	@Override
	public List<TbAnnouncement> findAnByWord(String content) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql="select *from announcement where title like ? or content like ?";
		

		return qr.query(sql,new BeanListHandler<TbAnnouncement>(TbAnnouncement.class,processor),"%"+content+"%","%"+content+"%");
	}

	//得到总的数量
	@SuppressWarnings({ "unchecked", "rawtypes" })
	@Override
	public int count(String content) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql="select count(*) from announcement";
		List list=new ArrayList();
		//如果content不是空，就把条件加上
		if(!"".equals(content)){
			sql+=" where title like ? or content like ?";
			list.add("%"+content+"%");
			list.add("%"+content+"%");
		}
		long l = qr.query(sql, new ScalarHandler<Long>(1),list.toArray());
		return (int)l;
		
	}


	@Override
	public List<TbAnnouncement> showAnnounce(int pageSize) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql = "select * from announcement order by create_time desc limit 0,?";
		
		
		return  qr.query(sql, new BeanListHandler<TbAnnouncement>(TbAnnouncement.class,processor),pageSize);
	}

	@Override
	public TbAnnouncement showAnById(String announcementId) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql = "select * from announcement where announcement_id=?";
		

		return  qr.query(sql, new BeanHandler<TbAnnouncement>(TbAnnouncement.class,processor),announcementId);
	}

	@Override
	public List<TbAnnouncement> showAll(int currentPage, int pageSize) throws SQLException {
		
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql = "select * from announcement order by create_time limit ?,?";
		

		return  qr.query(sql, new BeanListHandler<TbAnnouncement>(TbAnnouncement.class,processor),(currentPage-1)*pageSize,pageSize);
	}

	@Override
	public int delAll(String[] ids) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		Object[][] params = new Object[ids.length][];
		for (int i = 0; i < params.length; i++) {
			params[i] = new Object[]{ids[i]};//循环给每个一维数组中的元素赋值，值是id
		}
		qr.batch("delete from announcement where announcement_id=?", params);
		
		return 1;
	}

}
